/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package bakenote.data;

import bakenote.core.*;

import java.io.BufferedReader;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author lello
 */
public class RecipeDB {

    // Using the built-in derby driver
    /**
    private static final String driverName = "org.apache.derby.jdbc.EmbeddedDriver";
    private static final String dbName = "bakenotedb";
    private static final String dbType = "derby:";
     **/

    // Using the sqlite driver provided by Xerial
    private static final String driverName = "org.sqlite.JDBC";
    private static final String dbName = "bakenote.db";
    private static final String dbType = "sqlite:";

    private static final String dbTypeUrl = "jdbc:"+dbType;

    private static String dbFullName = null;
    private static String dbStoragePath = null;
    private static String fileStoragePath = null;
    private static String dbUrl = null;
    private static Connection connection = null;
    private static Statement statement = null;

    private static final String insert_recipe_base = "INSERT INTO RECIPES.RECIPE_LIST";
    private static final String insert_formula_base = "INSERT INTO RECIPES.RECIPE_FORMULA";
    private static final String update_recipe_base = "UPDATE RECIPES.RECIPE_LIST SET ";
    private static final String[] recipeListField = {"RECIPE_UUID", "TITLE", "AUTHOR", "KIND", "TAGS", "SOURCE", "RATING"};


    public RecipeDB(String dbStoragePath) throws ClassNotFoundException {
        RecipeDB.dbStoragePath = dbStoragePath;
        RecipeDB.fileStoragePath = dbStoragePath+File.separator+"storage";
        dbFullName = RecipeDB.dbStoragePath + File.separator + dbName;
        dbUrl = dbTypeUrl + dbFullName;
        try {
            Class.forName(driverName).newInstance();
        } catch (Exception ex) {
            Logger.getLogger(RecipeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean exist() {
        File dbFile = new File(dbFullName);
        // if we use the derby driver
        //return dbFile.exists() && dbFile.isDirectory();
        return dbFile.exists() && dbFile.isFile();
    }

    public void connect() throws SQLException {
        connection = DriverManager.getConnection(dbUrl);
    }

    public static void openDatabase(String dbStoragePath) throws SQLException {
        RecipeDB.dbStoragePath = dbStoragePath;
        RecipeDB.fileStoragePath = dbStoragePath+File.separator+"storage";
        dbFullName = dbTypeUrl + dbStoragePath+File.separator+dbName;
        connection.close();
        connection = DriverManager.getConnection(dbFullName);
        loadRecipes();
        loadGroups();
    }

    public void close() {
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(RecipeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void create() {
        try {
            // add the clause create=true to the connection string
            System.out.println("db url: "+dbUrl);
            // to be used with derby
            //String newDbUrl = dbUrl + ";create=true";
            String newDbUrl = dbUrl;
            connection = DriverManager.getConnection(newDbUrl);
        } catch (SQLException ex) {
            Logger.getLogger(RecipeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }


    public void init() throws SQLException {

        // Here we create the SQL DATABASE schema
        String s = "";
        StringBuilder sb = new StringBuilder();
        //String[] SQLFiles = {"schema.sql", "hamelman.sql", "reinhart.sql", "dimuzio.sql"};
        String[] SQLFiles = {"schema-sqlite.sql"};

        // Create statement
        statement = connection.createStatement();

        // here we execute the statements contained into the SQL script files.
        // the SQL parser below is very primitive and has been tested only for my sql files.
        for (int j = 0; j < SQLFiles.length; j++) {
            try {
                String fname = "bakenote/data/sql/" + SQLFiles[j].trim();
                ClassLoader loader = RecipeDB.class.getClassLoader();
                System.out.println("sql file url " + loader.getResource(fname));
                InputStream is = loader.getResourceAsStream(fname);
                InputStreamReader isr = new InputStreamReader(is);
                BufferedReader br = new BufferedReader(isr);
                while ((s = br.readLine()) != null) {
                    sb.append(s);
                }
                br.close();
                // here is our splitter ! We use ";" as a delimiter for each request
                // then we are sure to have well formed statements
                String[] instruction = sb.toString().split(";");

                for (String inst : instruction) {
                    // we ensure that there is no spaces before or after the request string
                    // in order to not execute empty statements
                    if (!inst.trim().equals("")) {
                        statement.executeUpdate(inst);
                        System.out.println(">> "+inst);
                    }
                }

                // Reset the stringbuilder (buffer)
                if (sb.length() > 0) {
                    sb.delete(0, sb.length());
                }
            } catch (Exception e) {
                //System.out.println("*** Error : "+e.toString());
                //System.out.println("*** ");
                System.out.println("*** Error : ");
                //e.printStackTrace();
                System.out.println("################################################");
                System.out.println("Cannot read SQL schema file.");
                System.out.println(sb.toString());
            }
        }
    }

    /**
     * This method loads the recipes from the SQL database, and
     * creates a List<> of Recipe.
     */
    public static void loadRecipes() {

        Amount amount = null;

        try {
            Statement st = null;
            Statement stf = null;
            statement = connection.createStatement();
            ResultSet recipes = statement.executeQuery("select * from RECIPES.RECIPE_LIST");
            //ResultSetMetaData rsmd = recipes.getMetaData();
            while (recipes.next()) {
                // Load the recipes form the RECIPE_LIST TABLE
                Recipe r = new Recipe();
                r.setUUID(recipes.getString("RECIPE_UUID"));
                r.setName(recipes.getString("TITLE"));
                r.setAuthor(recipes.getString("AUTHOR"));
                r.setSource(recipes.getString("SOURCE"));
                r.setType(recipes.getString("KIND"));
                r.setRating(recipes.getInt("RATING"));
                // For each recipe look up the formula in the RECIPE_FORMULA TABLE
                st = connection.createStatement();
                // each recipe is divided into several formulae (at least one must be present)
                // Count the number of formulae in a recipe
                ResultSet formula = st.executeQuery("select * from RECIPES.RECIPE_FORMULA WHERE RECIPE_UUID=" + "'" + r.getUUID() + "'" + " AND PARENT=-1");
                // add formula to the recipe
                while (formula.next()) {
                    Formula f = new Formula();
                    f.setName(formula.getString("INGREDIENT"));
                    stf = connection.createStatement();
                    //System.out.println("select * from RECIPES.RECIPE_FORMULA WHERE RECIPE_UUID="+"'"+r.getUUID()+"'"+" AND PARENT="+formula.getInt("INGREDIENT_ID"));
                    ResultSet rf = stf.executeQuery("select * from RECIPES.RECIPE_FORMULA WHERE RECIPE_UUID=" + "'" + r.getUUID() + "'" + " AND PARENT=" + formula.getInt("INGREDIENT_ID"));
                    while (rf.next()) {
                        //System.out.println(rf.getString("INGREDIENT"));
                        //f.addIngredient(new Ingredient(rf.getString("INGREDIENT"),rf.getInt("AMOUNT")));
                        String amount_type = rf.getString("AMOUNT_TYPE");
                        if (amount_type.equals("s")) {
                            amount = Amount.valueOf(rf.getString("AMOUNT_STR"));
                        } else if (amount_type.equals("n")) {
                            amount = Amount.valueOf(rf.getDouble("AMOUNT_NUM"));
                        }
                        Ingredient ing = new Ingredient(rf.getString("INGREDIENT"), amount, rf.getString("UNIT"));
                        if (rf.getInt("REFERENCE_INGR") == 1) ing.setReference(true);
                        f.addIngredient(ing);
                    }
                    //System.out.println("ing num "+f.getIngredients().getLength());
                    r.addFormula(f);
                    stf.close();
                }
                st.close();
                RecipeList.put(r.getUUID(), r);
            }
            recipes.close();
            statement.close();
        } catch (SQLException ex) {
            Logger.getLogger(RecipeDB.class.getName()).log(Level.SEVERE, null, ex);
        }

        // Process the formulae in each recipe to set the percentage
        for (Recipe r : RecipeList.getList()) {
            double totAmount = r.getAmount();
            for (Formula f : r.getFormulaList()) {
                f.BakersPercent();
                f.setPercent(f.getAmount() / totAmount);
            }
        }

    }

    public static void saveRecipe(Recipe r) throws SQLException {

        // define the SQL statements
        statement = connection.createStatement();

        String actualFields = "";
        String insertValues = "";

        actualFields = actualFields + recipeListField[0];
        insertValues += "'" + r.getUUID() + "'";

        actualFields += "," + recipeListField[1];
        insertValues += "," + "'" + r.getName().replace("'", "''") + "'";

        if (!r.getAuthor().isEmpty()) {
            actualFields += "," + recipeListField[2];
            insertValues += "," + "'" + r.getAuthor().replace("'", "''") + "'";
        }

        if (!r.getType().isEmpty()) {
            actualFields += "," + recipeListField[3];
            insertValues += "," + "'" + r.getType().replace("'", "''") + "'";
        }

        if (!r.getTag().isEmpty()) {
            actualFields += "," + recipeListField[4];
            String gtag = r.getTag().replace("'", "''");
            insertValues += "," + "'" + gtag + "'";
        }

        if (!r.getSource().isEmpty()) {
            actualFields += "," + recipeListField[5];
            insertValues += "," + "'" + r.getSource().replace("'", "''") + "'";
        }

        actualFields += "," + recipeListField[6];
        insertValues += "," + r.getRating();

        String rinstruction = insert_recipe_base + "(" + actualFields + ")" + " VALUES (" + insertValues + ")";
        //System.out.println(rinstruction);

        // execute the instruction
        statement.executeUpdate(rinstruction);

        // Now we stard to construct the recipe
        int fid = 0;
        for (Formula f : r.getFormulaList()) {
            // example
            //INSERT INTO RECIPE_FORMULA (INGREDIENT_ID, INGREDIENT, RECIPE_UUID) VALUES( 1, 'MAIN_FORMULA', 172);
            String finstruction = insert_formula_base + "(INGREDIENT_ID, INGREDIENT, RECIPE_UUID) VALUES ( ";
            ++fid;
            finstruction += fid + ",";
            finstruction += "'" + f.getName().replace("'", "''") + "'" + ",";
            finstruction += "'" + r.getUUID() + "'" + ")";
            //System.out.println("finst "+ finstruction);
            statement.executeUpdate(finstruction);
            finstruction = null;
            int iid = 0;
            for (Ingredient in : f.getIngredients()) {
                //(INGREDIENT_ID, INGREDIENT, PARENT, REFERENCE_INGR, AMOUNT, RECIPE_UUID) VALUES(18, 'Uova', 17, 0,  250, 174);
                //finstruction = insert_formula_base  + "(INGREDIENT_ID, INGREDIENT, PARENT, REFERENCE_INGR, AMOUNT, RECIPE_UUID) VALUES (";
                finstruction = insert_formula_base + "(INGREDIENT_ID, INGREDIENT, PARENT, REFERENCE_INGR, AMOUNT_TYPE, AMOUNT_NUM, AMOUNT_STR, UNIT, RECIPE_UUID) VALUES (";
                ++iid;
                finstruction += iid + ",";
                finstruction += "'" + in.getName().replace("'", "''") + "'" + ",";
                finstruction += fid + ",";
                finstruction += in.isReference() ? "1," : "0,";
                String atype = in.getAmount().getType();
                finstruction += "'" + atype + "'" + ",";
                if (atype.equals("n")) {
                    finstruction += in.getAmount().getDouble() + ",";
                    finstruction += null + ",";
                } else if (atype.equals("s")) {
                    finstruction += null + ",";
                    finstruction += "'" + in.getAmount().getSymbol() + "'" + ",";
                }
                finstruction += "'" + in.getUnit().trim() + "'" + ",";
                finstruction += "'" + r.getUUID() + "'" + ")";
                //System.out.println("finst "+ finstruction);
                statement.executeUpdate(finstruction);
            }
        }

        // close the connection with the db
        statement.close();

    }

    public static void deleteRecipe(Recipe r) {
        // Remove the selected recipe from the List and from the database
        try {
            // open the connection
            statement = connection.createStatement();
            //First remove the recipe from any group to which it belongs
            String removeFromGroup = "DELETE FROM RECIPES.RECIPE_GROUP_MEMBERS WHERE RECIPE_UUID=" + "'" + r.getUUID() + "'";
            statement.executeUpdate(removeFromGroup);

            // Second: remove all recipe formula
            String rinstr = "";
            rinstr = "DELETE FROM RECIPES.RECIPE_FORMULA WHERE RECIPE_UUID=" + "'" + r.getUUID() + "'";
            statement.executeUpdate(rinstr);

            // Finally: remove recipe from the list
            rinstr = "DELETE FROM RECIPES.RECIPE_LIST WHERE RECIPE_UUID=" + "'" + r.getUUID() + "'";
            statement.executeUpdate(rinstr);
            File recipeDir = new File(RecipeDB.fileStoragePath + File.separator +r.getUUID());
            System.out.println("dir "+recipeDir);
            if (recipeDir.exists()) {
                System.out.println("removing files");
                File[] fileList = recipeDir.listFiles();
                for (int i = 0; i < fileList.length; i++) {
                    System.out.println("rm file "+fileList[i].toString());
                    fileList[i].delete();
                }
                recipeDir.delete();
            }
            statement.close();
        } catch (SQLException ex) {
            Logger.getLogger(RecipeDB.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void updateRecipe(Recipe r) throws SQLException {

        statement = connection.createStatement();

        String updateValue = "";

        updateValue += recipeListField[1] + "=" + "'" + r.getName().replace("'", "''") + "'";

        if (!r.getAuthor().isEmpty()) {
            updateValue += "," + recipeListField[2] + "=" + "'" + r.getAuthor().replace("'", "''") + "'";
        }

        if (!r.getType().isEmpty()) {
            updateValue += "," + recipeListField[3] + "=" + "'" + r.getType().replace("'", "''") + "'";
        }

        if (!r.getTag().isEmpty()) {
            String gtag = r.getTag().replace("'", "''");
            updateValue += "," + recipeListField[4] + "=" + "'" + gtag + "'";
        }

        if (!r.getSource().isEmpty()) {
            updateValue += "," + recipeListField[5] + "=" + "'" + r.getSource().replace("'", "''") + "'";
        }

        updateValue += "," + recipeListField[6] + "=" + r.getRating();

        String rinstruction = update_recipe_base + updateValue + " WHERE RECIPE_UUID=" + "'" + r.getUUID() + "'";

        // execute the instruction
        statement.executeUpdate(rinstruction);

        // To update the recipe formula it is easier to delete and rewrite completely
        String rinstr = "DELETE FROM RECIPES.RECIPE_FORMULA WHERE RECIPE_UUID=" + "'" + r.getUUID() + "'";
        statement.executeUpdate(rinstr);

        int fid = 0;
        for (Formula f : r.getFormulaList()) {
            // example
            //INSERT INTO RECIPE_FORMULA (INGREDIENT_ID, INGREDIENT, RECIPE_UUID) VALUES( 1, 'MAIN_FORMULA', 172);
            String finstruction = insert_formula_base + "(INGREDIENT_ID, INGREDIENT, RECIPE_UUID) VALUES ( ";
            ++fid;
            finstruction += fid + ",";
            finstruction += "'" + f.getName().replace("'", "''") + "'" + ",";
            finstruction += "'" + r.getUUID() + "'" + ")";
            //System.out.println("finst "+ finstruction);
            statement.executeUpdate(finstruction);
            finstruction = null;
            int iid = 0;
            for (Ingredient in : f.getIngredients()) {
                //(INGREDIENT_ID, INGREDIENT, PARENT, REFERENCE_INGR, AMOUNT, RECIPE_UUID) VALUES(18, 'Uova', 17, 0,  250, 174);
                //finstruction = insert_formula_base  + "(INGREDIENT_ID, INGREDIENT, PARENT, REFERENCE_INGR, AMOUNT, RECIPE_UUID) VALUES (";
                finstruction = insert_formula_base + "(INGREDIENT_ID, INGREDIENT, PARENT, REFERENCE_INGR, AMOUNT_TYPE, AMOUNT_NUM, AMOUNT_STR, UNIT, RECIPE_UUID) VALUES (";
                ++iid;
                finstruction += iid + ",";
                finstruction += "'" + in.getName().replace("'", "''") + "'" + ",";
                finstruction += fid + ",";
                finstruction += in.isReference() ? "1," : "0,";
                String atype = in.getAmount().getType().replace("'", "''");
                finstruction += "'" + atype + "'" + ",";
                if (atype.equals("n")) {
                    finstruction += in.getAmount().getDouble() + ",";
                    finstruction += null + ",";
                } else if (atype.equals("s")) {
                    finstruction += null + ",";
                    finstruction += "'" + in.getAmount().getSymbol() + "'" + ",";
                }
                finstruction += "'" + in.getUnit().trim() + "'" + ",";
                finstruction += "'" + r.getUUID() + "'" + ")";
                statement.executeUpdate(finstruction);
            }
        }

        // close the connection with the db
        statement.close();

    }

    public static void saveGroup(RecipeGroup g) throws SQLException {

        // define the SQL statements
        statement = connection.createStatement();

        final String insert_group = "INSERT INTO RECIPES.RECIPE_GROUPS (GROUP_UUID, GROUP_NAME) VALUES (" + "'" + g.getUUID() + "'" + "," + "'" + g.getName().replace("'", "''") + "'" + ")";
        statement.executeUpdate(insert_group);

        // close the connection with the db
        statement.close();

    }

    public static void deleteGroup(RecipeGroup g) {
        // Remove the selected recipe from the List and from the database
        try {
            String rinstr = "";
            statement = connection.createStatement();
            rinstr = "DELETE FROM RECIPES.RECIPE_GROUP_MEMBERS WHERE GROUP_UUID=" + "'" + g.getUUID() + "'";
            statement.executeUpdate(rinstr);
            rinstr = "DELETE FROM RECIPES.RECIPE_GROUPS WHERE GROUP_UUID=" + "'" + g.getUUID() + "'";
            statement.executeUpdate(rinstr);
            // close the connection with the db
            statement.close();
        } catch (SQLException ex) {
            Logger.getLogger(RecipeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void updateGroupName(RecipeGroup g, String newName) throws SQLException {

        // update the group name
        statement = connection.createStatement();
        String instruction = "SELECT * FROM RECIPES.RECIPE_GROUPS WHERE GROUP_UUID = " + "'" + g.getUUID() + "'";
        ResultSet resultSet = statement.executeQuery(instruction);
        resultSet.next();
        String groupName = resultSet.getString("GROUP_NAME");
        // update name
        if (!g.getName().equals(groupName)) {
            String nameUpdateInstruction = "UPDATE RECIPES.RECIPE_GROUPS SET GROUP_NAME=" + "'" + newName.replace("'", "''") + "' WHERE GROUP_UUID=" + "'" + g.getUUID() + "'";
            //System.out.println("update name instr: "+nameUpdateInstruction);
            statement.executeUpdate(nameUpdateInstruction);
        }
    }

    public static void updateGroupMembers(RecipeGroup g) throws SQLException {

        // open the connection
        statement = connection.createStatement();
        String instruction = "SELECT * FROM RECIPES.RECIPE_GROUP_MEMBERS WHERE GROUP_UUID = " + "'" + g.getUUID() + "'";
        ResultSet resultSet = statement.executeQuery(instruction);
        List<String> uuidList = new ArrayList<String>();
        // First remove recipes which are not anymore in the group...
        while (resultSet.next()) {
            String recipeID = resultSet.getString("RECIPE_UUID");
            uuidList.add(recipeID);
            if (!g.contains(recipeID)) {
                String removeFromGroup = "DELETE FROM RECIPES.RECIPE_GROUP_MEMBERS WHERE GROUP_UUID=" + "'" + g.getUUID() + "'";
                statement.executeUpdate(removeFromGroup);
            }
            ;
        }
        // ...then add new recipes
        for (Recipe r : g.getList()) {
            if (!uuidList.contains(r.getUUID())) {
                String addRecipeToGroupInstruction = "INSERT INTO RECIPES.RECIPE_GROUP_MEMBERS (GROUP_UUID, RECIPE_UUID) VALUES (" + "'" + g.getUUID() + "'" + "," + "'" + r.getUUID() + "'" + ")";
                //System.out.println("update group "+addRecipeToGroupInstruction);
                statement.executeUpdate(addRecipeToGroupInstruction);
            }
        }
        // close the connection with the db
        statement.close();
    }

    public static void loadGroups() {

        try {
            Statement st = null;
            statement = connection.createStatement();
            ResultSet recipeGroups = statement.executeQuery("select * from RECIPES.RECIPE_GROUPS");
            while (recipeGroups.next()) {
                RecipeGroup group = new RecipeGroup();
                group.setUUID(recipeGroups.getString("GROUP_UUID"));
                group.setName(recipeGroups.getString("GROUP_NAME"));
                // add the group from the database to the group list
                RecipeGroupList.add(group);
                st = connection.createStatement();
                ResultSet recipes = st.executeQuery("select * from RECIPES.RECIPE_GROUP_MEMBERS WHERE GROUP_UUID=" + "'" + group.getUUID() + "'");
                while (recipes.next()) {
                    String recipeUUID = recipes.getString("RECIPE_UUID");
                    group.add(RecipeList.getRecipe(recipeUUID));
                }
                st.close();
            }
            recipeGroups.close();
            statement.close();
        } catch (SQLException ex) {
            Logger.getLogger(RecipeDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}